package lib.book;

import lib.book.bookModule;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import jdbc.JdbcPort;

public class bookDao {
	public boolean insertNew(bookModule Book) throws SQLException{
		Connection conn = null;
        PreparedStatement pstmt = null;
        boolean res=false;
		try {
			conn=JdbcPort.getConnection();
			String sql="INSERT INTO libbook values(?,?,?,?,?,?,?,?,?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, Book.getBno());
			pstmt.setString(2, Book.getCategory());
			pstmt.setString(3, Book.getTitle());
			pstmt.setString(4, Book.getPublish());
			pstmt.setInt(5, Book.getYear());
			pstmt.setString(6, Book.getAuthor());
			pstmt.setDouble(7, Book.getPrice());
			pstmt.setInt(8, Book.getTotal());
			pstmt.setInt(9, Book.getStock());
			int result =pstmt.executeUpdate();
			if(result>0) {
				res=true;
				System.out.println("导入图书成功");
			}
			else {
				System.out.println("导入图书失败");
			}
		}catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return res;
	}
	
	public List<bookModule> searchBookByTitle(String title) {
		JdbcPort p= new JdbcPort();
		List<bookModule> bk= new ArrayList<bookModule>();
		String sql="SELECT * from libbook where title like ?";
		ResultSet rs= p.execQuery(sql, new Object[] {("%"+title+"%")});
		try {
			while(rs.next()) {
				bookModule book= new bookModule();
				book.setBno(rs.getString("bno"));
				book.setCategory(rs.getString("category"));
				book.setTitle(rs.getString("title"));
				book.setPublish(rs.getString("publish"));
				book.setYear(rs.getInt("year"));
				book.setAuthor(rs.getString("author"));
				book.setPrice(rs.getDouble("price"));
				book.setTotal(rs.getInt("total"));
				book.setStock(rs.getInt("stock"));
				bk.add(book);
			}
			return bk;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}
	
	public List<bookModule> searchBookByBno(String bno) {
		JdbcPort p= new JdbcPort();
		List<bookModule> bk= new ArrayList<bookModule>();
		String sql="SELECT * from libbook where bno =?";
		ResultSet rs= p.execQuery(sql, new Object[] {bno});
		try {
			while(rs.next()) {
				bookModule book= new bookModule();
				book.setBno(rs.getString("bno"));
				book.setCategory(rs.getString("category"));
				book.setTitle(rs.getString("title"));
				book.setPublish(rs.getString("publish"));
				book.setYear(rs.getInt("year"));
				book.setAuthor(rs.getString("author"));
				book.setPrice(rs.getDouble("price"));
				book.setTotal(rs.getInt("total"));
				book.setStock(rs.getInt("stock"));
				bk.add(book);
			}
			return bk;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}
	
	public List<bookModule> searchBookByAuthor(String author) {
		JdbcPort p= new JdbcPort();
		List<bookModule> bk= new ArrayList<bookModule>();
		String sql="SELECT * from libbook where author=?";
		ResultSet rs= p.execQuery(sql, new Object[] {author});
		try {
			while(rs.next()) {
				bookModule book= new bookModule();
				book.setBno(rs.getString("bno"));
				book.setCategory(rs.getString("category"));
				book.setTitle(rs.getString("title"));
				book.setPublish(rs.getString("publish"));
				book.setYear(rs.getInt("year"));
				book.setAuthor(rs.getString("author"));
				book.setPrice(rs.getDouble("price"));
				book.setTotal(rs.getInt("total"));
				book.setStock(rs.getInt("stock"));
				bk.add(book);
			}
			return bk;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}
	
	public List<bookModule> searchBookByCategory(String category) {
		JdbcPort p= new JdbcPort();
		List<bookModule> bk= new ArrayList<bookModule>();
		String sql="SELECT * from libbook where category like ?";
		ResultSet rs= p.execQuery(sql, new Object[] {("%"+category+"%")});
		try {
			while(rs.next()) {
				bookModule book= new bookModule();
				book.setBno(rs.getString("bno"));
				book.setCategory(rs.getString("category"));
				book.setTitle(rs.getString("title"));
				book.setPublish(rs.getString("publish"));
				book.setYear(rs.getInt("year"));
				book.setAuthor(rs.getString("author"));
				book.setPrice(rs.getDouble("price"));
				book.setTotal(rs.getInt("total"));
				book.setStock(rs.getInt("stock"));
				bk.add(book);
			}
			return bk;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}
	
	public List<bookModule> searchBookByYear(int year) {
		JdbcPort p= new JdbcPort();
		List<bookModule> bk= new ArrayList<bookModule>();
		String sql="SELECT * from libbook where year = ? ";
		ResultSet rs= p.execQuery(sql, new Object[] {year});
		try {
			while(rs.next()) {
				bookModule book= new bookModule();
				book.setBno(rs.getString("bno"));
				book.setCategory(rs.getString("category"));
				book.setTitle(rs.getString("title"));
				book.setPublish(rs.getString("publish"));
				book.setYear(rs.getInt("year"));
				book.setAuthor(rs.getString("author"));
				book.setPrice(rs.getDouble("price"));
				book.setTotal(rs.getInt("total"));
				book.setStock(rs.getInt("stock"));
				bk.add(book);
			}
			return bk;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}
	
	public List<bookModule> searchBookByPrice(double price) {
		JdbcPort p= new JdbcPort();
		List<bookModule> bk= new ArrayList<bookModule>();
		String sql="SELECT * from libbook where price between ? and ?";
		ResultSet rs= p.execQuery(sql, new Object[] {(price-10),(price+10)});
		try {
			while(rs.next()) {
				bookModule book= new bookModule();
				book.setBno(rs.getString("bno"));
				book.setCategory(rs.getString("category"));
				book.setTitle(rs.getString("title"));
				book.setPublish(rs.getString("publish"));
				book.setYear(rs.getInt("year"));
				book.setAuthor(rs.getString("author"));
				book.setPrice(rs.getDouble("price"));
				book.setTotal(rs.getInt("total"));
				book.setStock(rs.getInt("stock"));
				bk.add(book);
			}
			return bk;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}
	
	public boolean updateStore(String bno, int flag) {
		boolean res=false;
		String sql=null;
		//flag=1：借书
		if(flag==1) {
			sql="update libbook set stock=stock-1 where stock>0 and bno=?";
		}
		//还书
		else {
			sql="update libbook set stock=stock+1 where stock<total and bno=?";
		}
		Connection conn=JdbcPort.getConnection();
		PreparedStatement pstmt = null;
		try {
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, bno);
			int result =pstmt.executeUpdate();
			//如果受到影响的行数不足1，说明借书操作失败/还书操作失败
			if(result>0) {
				res=true;
			}
		}catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
		return res;
	}
	
	public boolean updateBook(bookModule book) {
		boolean res=false;
		JdbcPort jp=new JdbcPort();
		String sql="update libbook set category=?, title=?, publish=?, year=?, author=?, price=?, total=?, stock=? where bno=?";
		int num=jp.execIorU(sql, new Object[]{book.getCategory(),book.getTitle(),book.getPublish(),book.getYear(),book.getAuthor(),book.getPrice(),book.getTotal(),book.getStock(),book.getBno()});
		if(num>0) {
			res=true;
		}
		return res;
	}
	
	public boolean updateMoreBook(String path)throws IOException {
		boolean res=false;
		List<bookModule> input_book=new ArrayList<>();
		String str;
		String [] info;
		int count=0;
		bookDao bk= new bookDao();
		File file=new File(path);
		FileInputStream fis=null;
		try {
			fis = new FileInputStream(file);
			BufferedReader br=new BufferedReader(new InputStreamReader(fis));
			while((str=br.readLine())!=null) {
				info=str.split(" ");
				bookModule temp = new bookModule(info[0],info[1],info[2],info[3],Integer.parseInt(info[4]),info[5],Double.parseDouble(info[6]),Integer.parseInt(info[7]),Integer.parseInt(info[8]));
				input_book.add(temp);
			}
			try{
				for(int i=0;i<input_book.size();i++) {
					res=bk.insertNew(input_book.get(i));
					if(res==false) {
						System.out.println("出错"+(i+1)+'\n');
					}
					else {
						count++;
					}
				}
			}catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			br.close();
		}catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		fis.close();
		if(count>0) {
			res=true;
		}
		return res;
	}
	
}
//	public static void main(String[] args)throws IOException {
//		List<bookModule> input_book=new ArrayList<>();
//		String str;
//		String [] info;
//		boolean res=false;
//		bookDao bk= new bookDao();
//		File file=new File("D:\\workplace\\school_library\\src\\lib\\book\\book.txt");
//		FileInputStream fis=null;
//		try {
//			fis = new FileInputStream(file);
//			BufferedReader br=new BufferedReader(new InputStreamReader(fis));
//			while((str=br.readLine())!=null) {
//				info=str.split(" ");
//				bookModule temp = new bookModule(info[0],info[1],info[2],info[3],Integer.parseInt(info[4]),info[5],Double.parseDouble(info[6]),Integer.parseInt(info[7]),Integer.parseInt(info[8]));
//				input_book.add(temp);
//			}
//			try{
//				for(int i=0;i<input_book.size();i++) {
//					res=bk.insertNew(input_book.get(i));
//					if(res==false) {
//						System.out.println("出错"+(i+1)+'\n');
//					}
//				}
//			}catch (SQLException e) {
//				// TODO Auto-generated catch block
//				e.printStackTrace();
//			}
//			br.close();
//		}catch (FileNotFoundException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//		fis.close();
//	}
	
//	public static void main (String []args) {
//		String str;
//		bookDao bd= new bookDao();
//		try {
//			InputStreamReader is = new InputStreamReader(System.in); //new构造InputStreamReader对象
//		    BufferedReader br = new BufferedReader(is); //拿构造的方法传到BufferedReader中，此时获取到的就是整个缓存流
//		    str = br.readLine();
//		    int x= Integer.parseInt(str);
//		    List<bookModule> bk = bd.searchBookByYear(x);
//		    if(bk==null) {
//		    	System.out.println("error");
//		    }
//		    else {
//		    	for(int i=0;i<bk.size();i++) {
//		    		System.out.println("category:" +(bk.get(i).getCategory())+'\n');
//		    	}
//		    }
//		    
//		}catch(IOException e){
//		      e.printStackTrace();
//	    }  
//	}

